package com.aizhixin.lab.company.controller;

import com.aizhixin.lab.account.dto.AccountDTO;
import com.aizhixin.lab.common.core.ApiReturnConstants;
import com.aizhixin.lab.common.core.DataValidity;
import com.aizhixin.lab.common.utils.ExcelUtil;
import com.aizhixin.lab.company.domain.FileDomain;
import com.aizhixin.lab.company.entity.User;
import com.aizhixin.lab.company.repository.UserRepository;
import com.aizhixin.lab.company.service.UserService;
import com.aizhixin.lab.config.Config;
import com.aizhixin.lab.course.course.entity.CourseChapterFile;
import com.aizhixin.lab.course.course.repository.CourseChapterFileRepository;
import com.aizhixin.lab.course.template.entity.*;
import com.aizhixin.lab.course.template.repository.*;
import com.aizhixin.lab.docker.entity.ChapterImages;
import com.aizhixin.lab.docker.repository.ChapterImagesRepository;
import com.aizhixin.lab.docs.entity.DocContent;
import com.aizhixin.lab.docs.repository.DocContentRepository;
import com.aizhixin.lab.jsrun.util.IpUtil;
import com.aizhixin.lab.jsrun.util.Md5Util;
import com.aizhixin.lab.project.course.service.TeacherProjectService;
import com.aizhixin.lab.task.domain.ImportTaskDomain;
import com.aizhixin.lab.task.domain.TaskQuestionDomain;
import com.aizhixin.lab.task.entity.Task;
import com.aizhixin.machine.Conversion;

import com.alibaba.fastjson.JSONObject;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiModelProperty;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;

import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.crypto.Data;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;


@RestController
@RequestMapping("/api/v1/init")
@Api(value = "初始化相关API")
public class InitController {
    @Autowired
    private UserService userService;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    RedisTemplate<String, String> redisTemplate;
    @Autowired
    Config config;
    @Autowired
    private TemplateProfRepository profRepository;
    @Autowired
    private TemplateRepository templateRepository;
    @Autowired
    private TemplateChapterRepository chapterRepository;
    @Autowired
    private TemplateChapterFileRepository chapterFileRepository;
    @Autowired
    private CourseChapterFileRepository courseChapterFileRepository;
    @Autowired
    private TemplateAssistRepository assistRepository;
    @Autowired
    private TemplateFileRepository fileRepository;
    @Autowired
    private ChapterImagesRepository imagesRepository;
    @Autowired
    private DocContentRepository docContentRepository;
    @Autowired
    private TeacherProjectService teacherProjectService;

    @RequestMapping(value = "/initadmin", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(httpMethod = "GET", value = "初始化超级管理员账号为labadmin,密码为dingli123", response = Void.class, notes = "初始化超级管理员账号为labadmin,密码为dingli123<br>@author zhengning")
    public ResponseEntity<?> initAdmin() {
        return new ResponseEntity(userService.initAdmin(), HttpStatus.OK);
    }


    @RequestMapping(value = "/getkey", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(httpMethod = "GET", value = "获取服务客户码", response = Void.class, notes = "获取服务客户码<br>@author zhengning")
    public ResponseEntity<?> getKey() {
        return new ResponseEntity(Conversion.getKey(), HttpStatus.OK);
    }


    @RequestMapping(value = "/verifykey", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(httpMethod = "GET", value = "验证注册码", response = Void.class, notes = "验证注册码<br>@author zhengning")
    public ResponseEntity<?> verifyKey(
            @ApiParam(value = "keyStr注册码", required = false) @RequestParam(value = "keyStr", required = true) String keyStr) {
        System.out.println(keyStr);
        return new ResponseEntity(Conversion.getResult(keyStr), HttpStatus.OK);
    }
    @RequestMapping(value = "/aab", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(httpMethod = "GET", value = "设置工单创建人", response = Void.class, notes = "设置工单创建人<br>@author zhengning")
    public ResponseEntity<?> aab(
            @ApiParam(value = "keyStr注册码", required = false) @RequestParam(value = "keyStr", required = true) String keyStr) {
        System.out.println(keyStr);
        teacherProjectService.aa(keyStr);
        return new ResponseEntity(null, HttpStatus.OK);
    }

    @RequestMapping(value = "/getStudentToken", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(httpMethod = "POST", value = "获取token(测试专用)", response = Void.class, notes = "获取学生的token<br>@author lwq")
    public ResponseEntity<?> getStudentToken(HttpServletRequest request) {
        AccountDTO accountDTO = new AccountDTO();
        accountDTO.setUserName("stu100");
        accountDTO.setName("测试学生");
        accountDTO.setRole("student");
        accountDTO.setPersonalProfile("6656565");
        accountDTO.setPassword("dingli123");
        accountDTO.setAccountId(2l);
        accountDTO.setId(12L);
        String ip = IpUtil.getIpAddress(request);
        String md5Token = Md5Util.md5(accountDTO.getUserName() + accountDTO.getPassword() + ip);
        JSONObject jsonObject = new JSONObject();
        String s = jsonObject.toJSONString(accountDTO);
        redisTemplate.opsForValue().set(md5Token, s, 1, TimeUnit.DAYS);
        return new ResponseEntity(md5Token, HttpStatus.OK);
    }

    @RequestMapping(value = "/getAllToken", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(httpMethod = "GET", value = "根据数据库ID获取token(测试专用)", response = Void.class, notes = "获取学生的token<br>@author lwq")
    public ResponseEntity<?> getToken(@ApiParam(value = "用户Id", required = false) @RequestParam(value = "username", required = true) String username
            , @RequestParam(value = "password", required = true) String password, HttpServletRequest request) {

        User user = userService.findByUserName(username);
        String onToken = "";
        if (user.getOntoken() == null) {
            onToken = Md5Util.md5(user.getUserName() + user.getPassWord() + UUID.randomUUID().toString());

        } else {
//            redisTemplate.delete(user.getOntoken());
            onToken = Md5Util.md5(user.getUserName() + user.getPassWord() + UUID.randomUUID().toString());
        }
        user.setOntoken(onToken);
//        userService.save(user);
        AccountDTO accountDTO = new AccountDTO();
        BeanUtils.copyProperties(user, accountDTO);
        JSONObject jsonObject = new JSONObject();
        String s = jsonObject.toJSONString(accountDTO);
        redisTemplate.opsForValue().set(onToken, s, 1, TimeUnit.DAYS);
        return new ResponseEntity(onToken, HttpStatus.OK);
    }

    @RequestMapping(value = "/getTestToken", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(httpMethod = "GET", value = "根据ID获取token(测试专用)", response = Void.class, notes = "获取用户的token<br>@author lwq")
    public ResponseEntity<?> getTestToken(@ApiParam(value = "用户Id", required = false) @RequestParam(value = "id", required = true) Long id) {

        User user = userRepository.findOne(id);
        String onToken = "";
        if (user.getOntoken() == null) {
            onToken = Md5Util.md5(user.getUserName() + user.getPassWord() + UUID.randomUUID().toString());

        } else {
//            redisTemplate.delete(user.getOntoken());
            onToken = Md5Util.md5(user.getUserName() + user.getPassWord() + UUID.randomUUID().toString());
        }
        user.setOntoken(onToken);
//        userService.save(user);
        AccountDTO accountDTO = new AccountDTO();
        BeanUtils.copyProperties(user, accountDTO);
        JSONObject jsonObject = new JSONObject();
        String s = jsonObject.toJSONString(accountDTO);
        redisTemplate.opsForValue().set(onToken, s, 1, TimeUnit.DAYS);
        return new ResponseEntity(onToken, HttpStatus.OK);
    }

    @RequestMapping(value = "/importFile", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "文件导入", httpMethod = "POST", notes = "文件导入<br><br><b>@author lwq</b>")
    public ResponseEntity<?> importTask(@ApiParam(value = "importTaskDomain") @RequestParam(value = "templateId") Long templateId,
                                        @ApiParam(value = "Excel数据文件", required = true) @RequestParam(value = "file") MultipartFile file) {

        List<FileDomain> list = new ArrayList<>();
        List<TemplateFile> templateFileList = new ArrayList<>();
        List<TemplateChapterFile> chapterFileList = new ArrayList<>();
        List<TemplateAssist> assistList = new ArrayList<>();
        try {
            File dest = new File(config.getBaseDir() + "/" + file.getOriginalFilename());
            FileUtils.copyInputStreamToFile(file.getInputStream(), dest);
            Workbook workbook = Workbook.getWorkbook(dest);
            Sheet sheet = workbook.getSheet(0);

            int rows = sheet.getRows();
            for (int i = 1; i < rows; i++) {
                FileDomain fileDomain = new FileDomain();
                if (sheet.getCell(4, i).getContents() == null || "".equals(sheet.getCell(4, i).getContents()) || "无".equals(sheet.getCell(4, i).getContents())) {
                    continue;
                }
                fileDomain.setTable(sheet.getCell(0, i).getContents().substring(2));
                fileDomain.setChapter(sheet.getCell(1, i).getContents());
                fileDomain.setType(sheet.getCell(2, i).getContents());
                fileDomain.setFileName(sheet.getCell(3, i).getContents());
                fileDomain.setUrl(sheet.getCell(4, i).getContents());
                list.add(fileDomain);
            }

            List<TemplateChapter> templateChapters = chapterRepository.findByTemplateId(templateId);
            Map<String, Long> map = new HashMap<>();
            Map<Long, Integer> chapterNo = new HashMap<>();
            for (TemplateChapter chapter : templateChapters) {
                map.put(chapter.getName(), chapter.getId());
                chapterNo.put(chapter.getId(), getFileNoMin(chapter.getId()));
            }
            for (FileDomain fileDomain : list) {
                if ("教学手册".equals(fileDomain.getTable())) {
                    TemplateFile templateFile = new TemplateFile();
                    templateFile.setName(fileDomain.getFileName());
                    templateFile.setFileUrl(fileDomain.getUrl());
                    templateFile.setTemplateId(templateId);
                    templateFileList.add(templateFile);

                }
//                else if ("教学内容".equals(fileDomain.getTable())){
//                    TemplateChapterFile templateChapterFile = new TemplateChapterFile();
//                    templateChapterFile.setName(fileDomain.getFileName());
//                    Long aLong = map.get(fileDomain.getChapter().substring(5));
//                    templateChapterFile.setChapterId(aLong);
//                    templateChapterFile.setType(type(fileDomain.getType()));
//                    templateChapterFile.setFileUrl(fileDomain.getUrl());
//                    templateChapterFile.setNo(chapterNo.get(templateChapterFile.getChapterId()));
//                    chapterNo.put(templateChapterFile.getChapterId(),templateChapterFile.getNo()-1);
//                    chapterFileList.add(templateChapterFile);
//                }else if ("其他资料".equals(fileDomain.getTable())){
//                    TemplateAssist assist = new TemplateAssist();
//                    assist.setName(fileDomain.getFileName());
//                    assist.setParentId(null);
//                    assist.setIsDir(false);
//                    assist.setTemplateId(templateId);
//                    assist.setFileUrl(fileDomain.getUrl());
//                    assistList.add(assist);
//                }
            }
            if (dest != null) {
                dest.delete();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (TemplateFile t : templateFileList) {
            System.out.println(t.toString());
        }
        for (TemplateChapterFile t : chapterFileList) {
            System.out.println(t.toString());
        }
        for (TemplateAssist t : assistList) {
            System.out.println(t.toString());
        }
        fileRepository.save(templateFileList);
        chapterFileRepository.save(chapterFileList);
        assistRepository.save(assistList);
//        System.out.println(list.toString());
//        Task task = taskService.save(domain, account, list.size());
//        List list1 = taskService.saveQuestionAndAnswer(list, task, account);
        return new ResponseEntity(list, HttpStatus.OK);
    }

    private Integer getFileNoMin(Long chapterId) {
        List<TemplateChapterFile> list = chapterFileRepository.findByChapterIdOrderByNo(chapterId);
        if (list != null && !list.isEmpty()) {
            TemplateChapterFile file = list.get(0);
            if (file != null && file.getNo() != null) {
                return file.getNo();
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }

    private Integer type(String name) {
        switch (name) {
            case "01PPT":
                return 10;
            case "02视频":
                return 20;
            case "03任务手册":
                return 30;
            case "04任务指导手册":
                return 40;
            case "06实验手册":
                return 30;
            case "07实验指导手册":
                return 40;
            case "05源码":
                return 50;
            default:
                return 50;
        }

    }

    @RequestMapping(value = "/importChapterFile", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "文件导入(有小节)", httpMethod = "POST", notes = "文件导入(有小节)<br><br><b>@author lwq</b>")
    public ResponseEntity<?> importFile(@ApiParam(value = "importTaskDomain") @RequestParam(value = "templateId") Long templateId,
                                        @ApiParam(value = "Excel数据文件", required = true) @RequestParam(value = "file") MultipartFile file) {

        List<FileDomain> list = new ArrayList<>();
        List<TemplateFile> templateFileList = new ArrayList<>();
        List<TemplateChapterFile> chapterFileList = new ArrayList<>();
        List<TemplateAssist> assistList = new ArrayList<>();
        try {
            File dest = new File(config.getBaseDir() + "/" + file.getOriginalFilename());
            FileUtils.copyInputStreamToFile(file.getInputStream(), dest);
            Workbook workbook = Workbook.getWorkbook(dest);
            Sheet sheet = workbook.getSheet(0);

            int rows = sheet.getRows();
            for (int i = 1; i < rows; i++) {
                FileDomain fileDomain = new FileDomain();
                if (sheet.getCell(5, i).getContents() == null || "".equals(sheet.getCell(5, i).getContents()) || "无".equals(sheet.getCell(5, i).getContents())) {
                    continue;
                }
                fileDomain.setTable(sheet.getCell(0, i).getContents().substring(2));
                fileDomain.setChapter(sheet.getCell(1, i).getContents());
                fileDomain.setType(sheet.getCell(2, i).getContents());
                fileDomain.setMinChapter(sheet.getCell(3, i).getContents());
                fileDomain.setFileName(sheet.getCell(4, i).getContents());
                fileDomain.setUrl(sheet.getCell(5, i).getContents());
                list.add(fileDomain);
            }

            List<TemplateChapter> templateChapters = chapterRepository.findByTemplateId(templateId);
            Map<String, TemplateChapter> map = new HashMap<>();
            Map<Long, Integer> chapterNo = new HashMap<>();
            Map<Integer, TemplateChapter> chapterMap = new HashMap<>();
            for (TemplateChapter chapter : templateChapters) {
                if (chapter.getType() == 10) {
                    map.put(chapter.getName(), chapter);
                } else {
                    chapterMap.put(chapter.getOrderNo(), chapter);
                }

                chapterNo.put(chapter.getId(), getFileNoMin(chapter.getId()));

            }
            for (FileDomain fileDomain : list) {
                if ("教学手册".equals(fileDomain.getTable())) {
                    TemplateFile templateFile = new TemplateFile();
                    templateFile.setName(fileDomain.getFileName());
                    templateFile.setFileUrl(fileDomain.getUrl());
                    templateFile.setTemplateId(templateId);
                    templateFileList.add(templateFile);

                } else if ("课程内容".equals(fileDomain.getTable())) {
                    TemplateChapterFile templateChapterFile = new TemplateChapterFile();
                    templateChapterFile.setName(fileDomain.getFileName());
                    TemplateChapter chapter = map.get(fileDomain.getChapter().substring(2));
                    int i = 0;
                    if (!"".equals(fileDomain.getMinChapter())) {
                        i = Integer.parseInt(fileDomain.getMinChapter());
                    }
//                    if (Objects.isNull(chapter)){
//                        chapter=new TemplateChapter();
//                        chapter.setOrderNo(7);
//                        chapter.setId(410L);
//                    }
                    Integer l = chapter.getOrderNo() * 10 + i;
                    TemplateChapter minChapter = chapterMap.get(l);
                    if (!Objects.isNull(minChapter)) {
                        templateChapterFile.setChapterId(minChapter.getId());
                    } else {
                        templateChapterFile.setChapterId(chapter.getId());
                    }

                    templateChapterFile.setType(type(fileDomain.getType()));
                    templateChapterFile.setFileUrl(fileDomain.getUrl());
                    templateChapterFile.setNo(chapterNo.get(templateChapterFile.getChapterId()));
                    chapterNo.put(templateChapterFile.getChapterId(), templateChapterFile.getNo() - 1);
                    chapterFileList.add(templateChapterFile);
                } else if ("其他内容".equals(fileDomain.getTable())) {
                    TemplateAssist assist = new TemplateAssist();
                    assist.setName(fileDomain.getFileName());
                    assist.setParentId(null);
                    assist.setIsDir(false);
                    assist.setTemplateId(templateId);
                    assist.setFileUrl(fileDomain.getUrl());
                    assistList.add(assist);
                }
            }
            if (dest != null) {
                dest.delete();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (TemplateFile t : templateFileList) {
            System.out.println(t.toString());
        }
        for (TemplateChapterFile t : chapterFileList) {
            System.out.println(t.toString());
        }
        for (TemplateAssist t : assistList) {
            System.out.println(t.toString());
        }
//        fileRepository.save(templateFileList);
        chapterFileRepository.save(chapterFileList);
//        assistRepository.save(assistList);
//        System.out.println(list.toString());
//        Task task = taskService.save(domain, account, list.size());
//        List list1 = taskService.saveQuestionAndAnswer(list, task, account);
        return new ResponseEntity(list, HttpStatus.OK);
    }

    @RequestMapping(value = "/bb", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "获取课程文件数量", httpMethod = "GET", notes = "获取课程文件数量<br><br><b>@author lwq</b>")
    public ResponseEntity<?> bb(HttpServletResponse response) throws IOException {
        Map<String, Object> result = new HashMap<String, Object>();
        List<TemplateChapterFile> fileList = chapterFileRepository.findByFileUrlStartingWithAndDeleteFlag("e1510bdd", 0);
        List<Template> templateList = templateRepository.findByDeleteFlagAndCreatedBy(0, 4L);

        fileList.size();
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("课程");
        HSSFRow row = sheet.createRow(0);
        row.createCell(1).setCellValue("课程名");
        row.createCell(0).setCellValue("序号");
        row.createCell(2).setCellValue("PPT");
        row.createCell(3).setCellValue("视频");
        row.createCell(4).setCellValue("任务手册");
        row.createCell(5).setCellValue("任务指导手册");
        int i = 1;
        for (Template t : templateList) {
            List<TemplateChapter> chapterList = chapterRepository.findByTemplateIdAndDeleteFlag(t.getId(), 0);
            List<Long> collect = chapterList.stream().map(TemplateChapter::getId).collect(Collectors.toList());
            Integer ppt = chapterFileRepository.countByChapterIdInAndTypeAndDeleteFlag(collect, 10, 0);
            Integer video = chapterFileRepository.countByChapterIdInAndTypeAndDeleteFlag(collect, 20, 0);
            Integer task = chapterFileRepository.countByChapterIdInAndTypeAndDeleteFlag(collect, 30, 0);
            Integer task2 = chapterFileRepository.countByChapterIdInAndTypeAndDeleteFlag(collect, 40, 0);
            HSSFRow row3 = sheet.createRow(i);
            row3.createCell(1).setCellValue(t.getName());
            row3.createCell(0).setCellValue(i);
            row3.createCell(2).setCellValue(ppt);
            row3.createCell(3).setCellValue(video);
            row3.createCell(4).setCellValue(task);
            row3.createCell(5).setCellValue(task2);
            i++;
        }


        FileOutputStream output = new FileOutputStream(config.getBaseDir() + "/课程文件数.xls");
        wb.write(output);
        output.flush();
        String basePath = config.getBaseDir() + "/课程文件数.xls";
        File file = new File(basePath);
        // 取得文件名。
        String filename = file.getName().toString();

        // 取得文件的后缀名。
        String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();

        // 以流的形式下载文件。
        InputStream fis = new BufferedInputStream(new FileInputStream(basePath));
        byte[] buffer = new byte[fis.available()];
        fis.read(buffer);
        fis.close();
        // 清空response
        response.reset();
        // 设置response的Header
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(System.currentTimeMillis() + ".xls", "UTF-8"));
        response.addHeader("Content-Length", "" + file.length());
        OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
        response.setContentType("application/octet-stream");
        toClient.write(buffer);
        toClient.flush();
        toClient.close();

        return new ResponseEntity(null, HttpStatus.OK);

    }

    @RequestMapping(value = "/aa", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "获取视频vid", httpMethod = "GET", notes = "获取视频vid<br><br><b>@author lwq</b>")
    public ResponseEntity<?> exportQuestions(HttpServletResponse response) throws IOException {
        Map<String, Object> result = new HashMap<String, Object>();
        List<TemplateChapterFile> fileList = chapterFileRepository.findByFileUrlStartingWithAndDeleteFlag("e1510bdd", 0);
        fileList.size();
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("vid");
        int i = 0;
        System.out.println(fileList.size());
        for (TemplateChapterFile p : fileList) {

            if (p.getFileUrl().startsWith("e1510bdd")) {
                HSSFRow row1 = sheet.createRow(i + 1);
                row1.createCell(0).setCellValue(p.getFileUrl());
                i++;
            }


        }
        System.out.println(i);
        FileOutputStream output = new FileOutputStream(config.getBaseDir() + "/vid.xls");
        wb.write(output);
        output.flush();
        String basePath = config.getBaseDir() + "/vid.xls";
        File file = new File(basePath);
        // 取得文件名。
        String filename = file.getName().toString();

        // 取得文件的后缀名。
        String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();

        // 以流的形式下载文件。
        InputStream fis = new BufferedInputStream(new FileInputStream(basePath));
        byte[] buffer = new byte[fis.available()];
        fis.read(buffer);
        fis.close();
        // 清空response
        response.reset();
        // 设置response的Header
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(System.currentTimeMillis() + ".xls", "UTF-8"));
        response.addHeader("Content-Length", "" + file.length());
        OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
        response.setContentType("application/octet-stream");
        toClient.write(buffer);
        toClient.flush();
        toClient.close();

        return new ResponseEntity(null, HttpStatus.OK);

    }

    @RequestMapping(value = "/exportTemplate", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "导出课程", httpMethod = "GET", notes = "导出课程<br><br><b>@author lwq</b>")
    public ResponseEntity<?> exportTemplate(@RequestParam Long templateId, HttpServletResponse response) throws IOException {
        Template template = templateRepository.findByIdAndDeleteFlag(templateId, DataValidity.VALID.getState());
        List<TemplateChapter> chapterList = chapterRepository.findByTemplateIdAndDeleteFlag(templateId, DataValidity.VALID.getState());
        List<Long> collect = chapterList.stream().map(TemplateChapter::getId).collect(Collectors.toList());
        List<TemplateAssist> assistList = assistRepository.findByTemplateIdAndDeleteFlag(templateId, DataValidity.VALID.getState());
        List<TemplateChapterFile> chapterFileList = chapterFileRepository.findByChapterIdIn(collect);
        List<TemplateFile> templateFileList = fileRepository.findByTemplateId(templateId);
        List<ChapterImages> chapterImagesList = imagesRepository.findByChapterIdIn(collect);

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet1 = wb.createSheet("template");
        HSSFSheet sheet2 = wb.createSheet("chapterList");
        HSSFSheet sheet3 = wb.createSheet("assistList");
        HSSFSheet sheet4 = wb.createSheet("chapterFileList");
        HSSFSheet sheet5 = wb.createSheet("templateFileList");
        HSSFSheet sheet6 = wb.createSheet("chapterImages");
        HSSFRow row = sheet1.createRow(1);
        HSSFRow row3 = sheet1.createRow(0);
        row3.createCell(1).setCellValue("模板ID");
        row3.createCell(0).setCellValue("序号");
        row3.createCell(2).setCellValue("模板名称");
        row3.createCell(3).setCellValue("课程类型10:理论课20:实验课");
        row3.createCell(4).setCellValue("课时");
        row3.createCell(5).setCellValue("章数");
        row3.createCell(6).setCellValue("知识点数");
        row3.createCell(7).setCellValue("课程图片");
        row3.createCell(8).setCellValue("课程简介");
        row.createCell(0).setCellValue(1);
        row.createCell(1).setCellValue(template.getId());
        row.createCell(2).setCellValue(template.getName());
        row.createCell(3).setCellValue(template.getType());
        row.createCell(4).setCellValue(template.getClassHour());
        row.createCell(5).setCellValue(template.getCountChapter());
        row.createCell(6).setCellValue(template.getCountSection());
        row.createCell(7).setCellValue(template.getImage());
        row.createCell(8).setCellValue(template.getIntro());
        Integer i = 1;
        row = sheet3.createRow(0);
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("辅助资料ID");
        row.createCell(2).setCellValue("课程ID");
        row.createCell(3).setCellValue("父类ID");
        row.createCell(4).setCellValue("是否是文件夹");
        row.createCell(5).setCellValue("名称");
        row.createCell(6).setCellValue("辅助资料key");
        row.createCell(7).setCellValue("辅助资料路径");
        for (TemplateAssist assist : assistList) {
            HSSFRow row1 = sheet3.createRow(i);
            row1.createCell(0).setCellValue(i);
            row1.createCell(1).setCellValue(assist.getId());
            row1.createCell(2).setCellValue(assist.getTemplateId());
            row1.createCell(3).setCellValue(assist.getParentId() == null ? 0 : assist.getParentId());
            row1.createCell(4).setCellValue(assist.getIsDir());
            row1.createCell(5).setCellValue(assist.getName());
            row1.createCell(6).setCellValue(assist.getFileKey() == null ? "" : assist.getFileKey());
            row1.createCell(7).setCellValue(assist.getFileUrl());
            i++;
        }
        i = 1;
        row = sheet2.createRow(0);
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("章ID");
        row.createCell(2).setCellValue("课程ID");
        row.createCell(3).setCellValue("父类ID");
        row.createCell(4).setCellValue("章名");
        row.createCell(5).setCellValue("章类型");
        row.createCell(6).setCellValue("是否是实验课");
        row.createCell(7).setCellValue("虚拟机名称");
        row.createCell(8).setCellValue("虚拟机数量");
        row.createCell(9).setCellValue("第几章");
        row.createCell(10).setCellValue("章序号");
        row.createCell(11).setCellValue("中文序号");
        for (TemplateChapter chapter : chapterList) {
            HSSFRow row1 = sheet2.createRow(i);
            row1.createCell(0).setCellValue(i);
            row1.createCell(1).setCellValue(chapter.getId());
            row1.createCell(2).setCellValue(chapter.getTemplateId());
            row1.createCell(3).setCellValue(chapter.getParentId() == null ? 0 : chapter.getParentId());
            row1.createCell(4).setCellValue(chapter.getName());
            row1.createCell(5).setCellValue(chapter.getType());
            row1.createCell(6).setCellValue(chapter.getIsExp());
            row1.createCell(7).setCellValue(chapter.getVmName() == null ? "" : chapter.getVmName());
            row1.createCell(8).setCellValue(chapter.getVmNum() == null ? 0 : chapter.getVmNum());
            row1.createCell(9).setCellValue(chapter.getChapterNo());
            row1.createCell(10).setCellValue(chapter.getOrderNo());
            row1.createCell(11).setCellValue(chapter.getChineseOrderNo());
            i++;
        }
        i = 1;
        row = sheet5.createRow(0);
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("课程文件ID");
        row.createCell(2).setCellValue("课程ID");
        row.createCell(3).setCellValue("文件名称");
        row.createCell(4).setCellValue("文件路径");
        row.createCell(5).setCellValue("文件");
        for (TemplateFile templateFile : templateFileList) {
            HSSFRow row1 = sheet5.createRow(i);
            row1.createCell(0).setCellValue(i);
            row1.createCell(1).setCellValue(templateFile.getId());
            row1.createCell(2).setCellValue(templateFile.getTemplateId());
            row1.createCell(3).setCellValue(templateFile.getName());
            row1.createCell(4).setCellValue(templateFile.getFileUrl());
            row1.createCell(5).setCellValue(templateFile.getFileKey());
            i++;
        }
        i = 1;
        row = sheet4.createRow(i);
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("章文件ID");
        row.createCell(2).setCellValue("章ID");
        row.createCell(3).setCellValue("文件名");
        row.createCell(4).setCellValue("文件类型");
        row.createCell(5).setCellValue("文件序号");
        row.createCell(6).setCellValue("文件路径");
        row.createCell(7).setCellValue("文件KEY");
        for (TemplateChapterFile chapterFile : chapterFileList) {
            HSSFRow row1 = sheet4.createRow(i);
            row1.createCell(0).setCellValue(i);
            row1.createCell(1).setCellValue(chapterFile.getId());
            row1.createCell(2).setCellValue(chapterFile.getChapterId());
            if (!StringUtils.isEmpty(chapterFile.getName()))
                row1.createCell(3).setCellValue(chapterFile.getName());
            if (!StringUtils.isEmpty(chapterFile.getType()))
                row1.createCell(4).setCellValue(chapterFile.getType());
            if (!StringUtils.isEmpty(chapterFile.getNo()))
                row1.createCell(5).setCellValue(chapterFile.getNo());
            if (!StringUtils.isEmpty(chapterFile.getFileUrl()))
                row1.createCell(6).setCellValue(chapterFile.getFileUrl());
            if (!StringUtils.isEmpty(chapterFile.getFileKey()))
                row1.createCell(7).setCellValue(chapterFile.getFileKey());
            i++;
        }
        i = 1;
        row = sheet6.createRow(0);
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("id");
        row.createCell(2).setCellValue("课程ID");
        row.createCell(3).setCellValue("章ID");
        row.createCell(4).setCellValue("镜像名称");
        row.createCell(5).setCellValue("镜像数量");
        row.createCell(6).setCellValue("创建人");
        row.createCell(7).setCellValue("过期时间");
        row.createCell(8).setCellValue("容器规格");
        row.createCell(9).setCellValue("超卖倍数");
        for (ChapterImages chapterImages : chapterImagesList) {
            HSSFRow row1 = sheet6.createRow(i);
            row1.createCell(0).setCellValue(i);
            row1.createCell(1).setCellValue(chapterImages.getId());
            row1.createCell(2).setCellValue(chapterImages.getCourseId());
            row1.createCell(3).setCellValue(chapterImages.getChapterId());
            row1.createCell(4).setCellValue(chapterImages.getImageName());
            row1.createCell(5).setCellValue(chapterImages.getNum());
            row1.createCell(6).setCellValue(chapterImages.getUserId());
            row1.createCell(7).setCellValue(chapterImages.getExpire());
            row1.createCell(8).setCellValue(chapterImages.getSpec());
            row1.createCell(9).setCellValue(chapterImages.getOverSold());
            i++;
        }

//        FileOutputStream output = new FileOutputStream(config.getBaseDir() + "/vid.xls");
        ServletOutputStream outputStream = response.getOutputStream();
        wb.write(outputStream);
//        output.flush();
//        String basePath = config.getBaseDir() + "/vid.xls";
//        File file = new File(basePath);
//        // 取得文件名。
//        String filename = file.getName().toString();
//
//        // 取得文件的后缀名。
//        String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
//
//        // 以流的形式下载文件。
//        InputStream fis = new BufferedInputStream(new FileInputStream(basePath));
//        byte[] buffer = new byte[fis.available()];
//        fis.read(buffer);
//        fis.close();
//        // 清空response
//        response.reset();
//        // 设置response的Header
//        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(System.currentTimeMillis() + ".xls", "UTF-8"));
//        response.addHeader("Content-Length", "" + file.length());
//        OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
//        response.setContentType("application/octet-stream");
//        toClient.write(buffer);
//        toClient.flush();
//        toClient.close();
        return new ResponseEntity(null, HttpStatus.OK);
    }

    @RequestMapping(value = "/importTemplate", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "文件导入", httpMethod = "POST", notes = "文件导入<br><br><b>@author lwq</b>")
    public ResponseEntity<?> importTemplate(@ApiParam(value = "version") @RequestParam(value = "version") String version,
                                            @ApiParam(value = "Excel数据文件", required = true) @RequestParam(value = "file") MultipartFile file) throws IOException {
        ExcelUtil excelUtil = new ExcelUtil(file.getInputStream(), version);
        org.apache.poi.ss.usermodel.Sheet sheetAt = excelUtil.getWorkbook().getSheetAt(0);
        org.apache.poi.ss.usermodel.Sheet sheetAt2 = excelUtil.getWorkbook().getSheetAt(1);
        org.apache.poi.ss.usermodel.Sheet sheetAt3 = excelUtil.getWorkbook().getSheetAt(2);
        org.apache.poi.ss.usermodel.Sheet sheetAt4 = excelUtil.getWorkbook().getSheetAt(3);
        org.apache.poi.ss.usermodel.Sheet sheetAt5 = excelUtil.getWorkbook().getSheetAt(4);
        org.apache.poi.ss.usermodel.Sheet sheetAt6 = excelUtil.getWorkbook().getSheetAt(5);
        int lastRowNum = sheetAt.getLastRowNum();
        Map<Long, Long> map = new HashMap<>();
        Map<Long, Long> assistMap = new HashMap<>();
        //template");
        //chapterList");
        //assistList");
        //chapterFileList");
        //templateFileList");
        Template template = new Template();
        List<TemplateChapter> chapterList = new ArrayList<>();
        List<TemplateAssist> assistList = new ArrayList<>();
        List<TemplateChapterFile> chapterFileList = new ArrayList<>();
        List<TemplateFile> templateFileList = new ArrayList<>();
        List<ChapterImages> chapterImagesList =new ArrayList<>();
        for (int i = 1; i <= lastRowNum; i++) {
            Row row = sheetAt.getRow(i);
            if (StringUtils.isEmpty(excelUtil.getCellValueToString(row.getCell(1)))) {
                continue;
            }
//            template.setId(Long.valueOf(excelUtil.getCellValueToString(row.getCell(1))));
            template.setName(excelUtil.getCellValueToString(row.getCell(2)));
            template.setType(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(3))));
            template.setClassHour(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(4))));
            template.setCountChapter(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(5))));
            template.setCountSection(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(6))));
            template.setImage(excelUtil.getCellValueToString(row.getCell(7)));
            template.setIntro(excelUtil.getCellValueToString(row.getCell(8)));
            template.setCreatedBy(4L);
            template.setIsPublish(false);
        }
        templateRepository.save(template);
        int lastRowNum1 = sheetAt2.getLastRowNum();
        for (int i = 1; i <= lastRowNum1; i++) {
            Row row = sheetAt2.getRow(i);
            if (StringUtils.isEmpty(excelUtil.getCellValueToString(row.getCell(1)))) {
                continue;
            }
            TemplateChapter chapter = new TemplateChapter();
//            chapter.setId(Long.valueOf(excelUtil.getCellValueToString(row.getCell(1))));
            chapter.setTemplateId(template.getId());
            chapter.setParentId(Long.valueOf(excelUtil.getCellValueToString(row.getCell(3))) == 0L ? null : map.get(Long.valueOf(excelUtil.getCellValueToString(row.getCell(3)))));
            chapter.setName(excelUtil.getCellValueToString(row.getCell(4)));
            chapter.setType(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(5))));
            chapter.setIsExp(excelUtil.getCellValueToString(row.getCell(6)) == "true" ? true : false);
            chapter.setVmName(excelUtil.getCellValueToString(row.getCell(7)));
            chapter.setVmNum(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(8))));
            chapter.setChapterNo(excelUtil.getCellValueToString(row.getCell(9)));
            chapter.setOrderNo(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(10))));
            chapter.setChineseOrderNo(excelUtil.getCellValueToString(row.getCell(11)));
            chapterList.add(chapter);
            chapterRepository.save(chapter);
            map.put(Long.valueOf(excelUtil.getCellValueToString(row.getCell(1))), chapter.getId());
        }

        int lastRowNum3 = sheetAt3.getLastRowNum();
        for (int i = 1; i <= lastRowNum3; i++) {
            Row row = sheetAt3.getRow(i);
            if (StringUtils.isEmpty(excelUtil.getCellValueToString(row.getCell(1)))) {
                continue;
            }
            TemplateAssist assist = new TemplateAssist();
//            assist.setId(Long.valueOf(excelUtil.getCellValueToString(row.getCell(1))));
            assist.setTemplateId(template.getId());
            assist.setParentId(Long.valueOf(excelUtil.getCellValueToString(row.getCell(3))) == 0L ? null : assistMap.get(Long.valueOf(excelUtil.getCellValueToString(row.getCell(3)))));
            assist.setIsDir(excelUtil.getCellValueToString(row.getCell(4)) == "true" ? true : false);
            assist.setName(excelUtil.getCellValueToString(row.getCell(5)));
            assist.setFileKey(excelUtil.getCellValueToString(row.getCell(6)));
            assist.setFileUrl(excelUtil.getCellValueToString(row.getCell(7)));
            assistList.add(assist);
            assistRepository.save(assist);
            assistMap.put(Long.valueOf(excelUtil.getCellValueToString(row.getCell(1))), assist.getId());

        }
        int lastRowNum4 = sheetAt4.getLastRowNum();
        for (int i = 1; i <= lastRowNum4; i++) {
            Row row = sheetAt4.getRow(i);
            if (StringUtils.isEmpty(excelUtil.getCellValueToString(row.getCell(1)))||StringUtils.isEmpty(excelUtil.getCellValueToString(row.getCell(4)))) {
                continue;
            }
            TemplateChapterFile chapterFile = new TemplateChapterFile();
//            chapterFile.setId(map.get(Long.valueOf(excelUtil.getCellValueToString(row.getCell(1)))));
            chapterFile.setChapterId(map.get(Long.valueOf(excelUtil.getCellValueToString(row.getCell(2)))));
            chapterFile.setName(excelUtil.getCellValueToString(row.getCell(3)));
            chapterFile.setType(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(4))));
            try {
                chapterFile.setNo(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(5))));
            }catch (NumberFormatException e){
                chapterFile.setNo(0);
            }
            chapterFile.setFileUrl(excelUtil.getCellValueToString(row.getCell(6)));
            chapterFile.setFileKey(excelUtil.getCellValueToString(row.getCell(7)));
            chapterFileList.add(chapterFile);
        }
        int lastRowNum5 = sheetAt5.getLastRowNum();
        for (int i = 1; i <= lastRowNum5; i++) {
            Row row = sheetAt5.getRow(i);
            if (StringUtils.isEmpty(excelUtil.getCellValueToString(row.getCell(1)))) {
                continue;
            }
            TemplateFile templateFile = new TemplateFile();
//            templateFile.setId(Long.valueOf(excelUtil.getCellValueToString(row.getCell(1))));
            templateFile.setTemplateId(template.getId());
            templateFile.setName(excelUtil.getCellValueToString(row.getCell(3)));
            templateFile.setFileUrl(excelUtil.getCellValueToString(row.getCell(4)));
            templateFile.setFileKey(excelUtil.getCellValueToString(row.getCell(5)));
            templateFileList.add(templateFile);
        }
        int lastRowNum6 = sheetAt6.getLastRowNum();
        for (int i = 1; i <= lastRowNum6; i++) {
            Row row = sheetAt6.getRow(i);
            ChapterImages chapterImages = new ChapterImages();
//            chapterImages.setId(excelUtil.getCellValueToString(row.getCell(1)));
            chapterImages.setCourseId(template.getId().toString());
            chapterImages.setChapterId(map.get(Long.parseLong(excelUtil.getCellValueToString(row.getCell(3)))));
            chapterImages.setImageName(excelUtil.getCellValueToString(row.getCell(4)));
            chapterImages.setNum(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(5))));
            chapterImages.setUserId(Long.parseLong(excelUtil.getCellValueToString(row.getCell(6))));
            chapterImages.setExpire(Long.parseLong(excelUtil.getCellValueToString(row.getCell(7))));
            chapterImages.setSpec(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(8))));
            chapterImages.setOverSold(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(9))));
            chapterImagesList.add(chapterImages);
        }
        fileRepository.save(templateFileList);
        chapterFileRepository.save(chapterFileList);
        imagesRepository.save(chapterImagesList);
        System.out.println(template);
        System.out.println(chapterList);
        System.out.println(assistList);
        System.out.println(chapterFileList);
        System.out.println(templateFileList);
        return new ResponseEntity<>(null, HttpStatus.OK);
    }

    @RequestMapping(value = "/replace", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "替换视频vid", httpMethod = "POST", notes = "文件导入<br><br><b>@author lwq</b>")
    public ResponseEntity<?> replace(@ApiParam(value = "version") @RequestParam(value = "version") String version,
                                     @ApiParam(value = "Excel数据文件", required = true) @RequestParam(value = "file") MultipartFile file) throws IOException {
        ExcelUtil excelUtil = new ExcelUtil(file.getInputStream(), version);

        org.apache.poi.ss.usermodel.Sheet sheetAt = excelUtil.getWorkbook().getSheetAt(0);
        Map<String, String> map = new HashMap<>();
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 0; i < lastRowNum; i++) {
            Row row = sheetAt.getRow(i);
            String key = excelUtil.getCellValueToString(row.getCell(1));
            String value = excelUtil.getCellValueToString(row.getCell(2));
            map.put(key, value);
        }
        List<TemplateChapterFile> chapterFileList = chapterFileRepository.findByFileUrlStartingWithAndDeleteFlag("3c30939b2d", DataValidity.VALID.getState());
        List<TemplateChapterFile> chapterFiles = new ArrayList<>();
        int c = 0;
        for (TemplateChapterFile t : chapterFileList) {
            if (map.get(t.getFileUrl()) != null) {
                t.setFileUrl(map.get(t.getFileUrl()));
                c++;
                chapterFiles.add(t);
            }
        }
        chapterFileRepository.save(chapterFiles);
        return new ResponseEntity<>(chapterFiles.size(), HttpStatus.OK);
    }

    @RequestMapping(value = "/replaceCourse", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "替换授课中视频vid", httpMethod = "POST", notes = "替换授课中视频vid<br><br><b>@author lwq</b>")
    public ResponseEntity<?> replaceCourse(@ApiParam(value = "version") @RequestParam(value = "version") String version,
                                           @ApiParam(value = "Excel数据文件", required = true) @RequestParam(value = "file") MultipartFile file) throws IOException {
        ExcelUtil excelUtil = new ExcelUtil(file.getInputStream(), version);
        org.apache.poi.ss.usermodel.Sheet sheetAt = excelUtil.getWorkbook().getSheetAt(0);
        Map<String, String> map = new HashMap<>();
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 0; i < lastRowNum; i++) {
            Row row = sheetAt.getRow(i);
            String key = excelUtil.getCellValueToString(row.getCell(1));
            String value = excelUtil.getCellValueToString(row.getCell(2));
            map.put(key, value);
        }
        int c = 0;
//        List<CourseChapterFile> courseChapterFileList = courseChapterFileRepository.findByChapterIdAndDeleteFlagOrderByNo("3c30939b2d", DataValidity.VALID.getState());
        List<DocContent> all = docContentRepository.findAll();
        List<DocContent> courseChapterFiles = new ArrayList<>();
        for (DocContent r : all) {
            if (map.get(r.getContent()) != null) {
                r.setContent(map.get(r.getContent()));
                c++;
                courseChapterFiles.add(r);
            }
        }
        docContentRepository.save(courseChapterFiles);
//        courseChapterFileRepository.save(courseChapterFiles);
        return new ResponseEntity<>(courseChapterFiles.size(), HttpStatus.OK);
    }
}
